﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyDuAn.DTO;
using QuanLyDuAn.DataAccess;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace QuanLyDuAn.BUS
{
    class VacancyController
    {
        DataSet ds;
        SqlCommand cmd;
        SqlDataAdapter da;
        //doc du lieu tu datasouce va doc du lieu tu dataset
        SqlConnection conn;
        dbAccess database = new dbAccess();
        SqlDataReader reader;
        public DataSet LoadVacancy()
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("getAllVacancy", conn);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Vacancy");
            return ds;
        }
        public DataSet Loadperiod()
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("getperiod", conn);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Vacancy");
            return ds;
        }
        public void InsertVacancy(String id, String date, String status, String title, String description, int amount, String department, String deadline)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("insertVacancy", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vId", SqlDbType.VarChar).Value = id;
            cmd.Parameters.Add("@vDate", SqlDbType.DateTime).Value = date;
            cmd.Parameters.Add("@vStatus", SqlDbType.VarChar).Value = status;
            cmd.Parameters.Add("@vTitle ", SqlDbType.VarChar).Value = title;
            cmd.Parameters.Add("@vDescription", SqlDbType.VarChar).Value = description;
            cmd.Parameters.Add("@vAmount", SqlDbType.Int).Value = amount;
            cmd.Parameters.Add("@vDepartment", SqlDbType.VarChar).Value = department;
            cmd.Parameters.Add("@vDeadline", SqlDbType.DateTime).Value = deadline;
            int i = cmd.ExecuteNonQuery();
        }

        public void UpdateVacancy(String id, String date, String status, String title, String description, int amount, String department, String deadline)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("updateVacancy", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vId", SqlDbType.VarChar).Value = id;
            cmd.Parameters.Add("@vDate", SqlDbType.DateTime).Value = date;
            cmd.Parameters.Add("@vStatus", SqlDbType.VarChar).Value = status;
            cmd.Parameters.Add("@vTitle ", SqlDbType.VarChar).Value = title;
            cmd.Parameters.Add("@vDescription", SqlDbType.VarChar).Value = description;
            cmd.Parameters.Add("@vAmount", SqlDbType.Int).Value = amount;
            cmd.Parameters.Add("@vDepartment", SqlDbType.VarChar).Value = department;
            cmd.Parameters.Add("@vDeadline", SqlDbType.DateTime).Value = deadline;
            int i = cmd.ExecuteNonQuery();
        
        }
        public void DeleteVacancy(String id)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("deleteVacancy", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vId", SqlDbType.VarChar).Value = id;
            int i = cmd.ExecuteNonQuery();
        }
        public DataSet SearchVacancyByStatus(String status)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("searchVacancyBySatus", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vStatus", SqlDbType.VarChar).Value = status;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Vacancy");
            return ds;
        }
        public DataSet SearchVacancyByTitile(String title)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("searchVacancyByTitle", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vTitle", SqlDbType.VarChar).Value = title;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Vacancy");
            return ds;
        }
        public DataSet SearchVacancyByDate(String date)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("searchVacancyByDate", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vDeadline", SqlDbType.DateTime).Value = date;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Vacancy");
            return ds;
        }
        public ArrayList GetTitleVacancy()
        {
            ArrayList list = new ArrayList();
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("getVacancyTitle", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                String title = reader.GetString(0);
                list.Add(title);
            }
            return list;
        }
        public DataSet SearchVacancyByDepartment (String department)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("searchVacancyByDepartment", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vDepartment", SqlDbType.VarChar).Value = department;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Vacancy");
            return ds;
        }
        public DataSet StatisticsVacancy(String datetime1,String datetime2)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("statisticsVacancy", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vDate1", SqlDbType.DateTime).Value = datetime1;
            cmd.Parameters.Add("@vDate2", SqlDbType.DateTime).Value = datetime2;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Vacancy");
            return ds;
        }
        public DataSet CheckVacancy(String vId)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("CheckVacancy", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vId", SqlDbType.VarChar).Value = vId;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Vacancy");
            return ds;
        }
    }
}
